Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Enabling Relational Access

If your analytic workspace must support ad hoc SQL queries and applications that do not use the OLAP API, you need to create views that present the workspace data in relational format.

Once you have created an analytic workspace cube and refreshed and aggregated its data, you can use the `DBMS_AWM` enablement procedures to create and maintain a set of views that can be queried with standard SQL. The `DBMS_AWM` enablement views include:

- An embedded total dimension view for each dimension hierarchy.

- An embedded total fact view for each combination of dimension hierarchies.

When you refresh a dimension or cube because of metadata change for its hierarchies, you must regenerate its enablement views. When you refresh a dimension or cube because of data changes, you can continue to use the pre-existing views.

If the enablement views do not provide the data in a useful format for your application, you can create your own views. Refer to Chapter 34, "OLAP_TABLE" for more information.

You can use `DBMS_AWM` enablement procedures to generate the enablement scripts and run them yourself, or you can use a one-step procedure to create and run the scripts automatically.

## Procedure: Generate and Run the Enablement Scripts

Use the following steps to create and run the enablement scripts for an analytic workspace:

1. Determine how your system is configured to write to files. The enabler procedures accept either a directory object or a directory path. If you specify a directory object, make sure that your user ID has been granted the appropriate access rights to it. If you specify a path, make sure that it is the value of the `UTL_FILE_DIR` initialization parameter for the instance.

2. Run the `REFRESH_AWCUBE` and `REFRESH_AWDIMENSION` procedures to refresh the cube. These procedures create metadata in the analytic workspace to track the generations of enablement view names.

3. The enablement process automatically provides system-generated names for the enablement views. To provide your own view names, call `REFRESH_AWDIMENSION_VIEW_NAME` and `REFRESH_AWCUBE_VIEW_NAME`, then call `SET_AWDIMENSION_VIEW_NAME` and `SET_AWCUBE_VIEW_NAME`.

4. Call the `CREATE_AWDIMENSION_ACCESS` procedure for each of the cube's dimensions. Each procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the dimension views and, optionally, an OLAP Catalog dimension that maps to the views.

5. Call the `CREATE_AWCUBE_ACCESS` procedure. This procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the fact views and, optionally, an OLAP Catalog cube that maps to the views.

6. Run the enablement scripts. The scripts will delete any previous generation of views and metadata before creating new views and metadata.

## Procedure: Run the Enablement Scripts Automatically

To create and run the enablement scripts automatically, use the following steps:

1. Refresh the cube and its dimensions in the analytic workspace, as described in step 2 of "Procedure: Generate and Run the Enablement Scripts".

2. If you want to specify your own view names, follow step 3 of "Procedure: Generate and Run the Enablement Scripts".

3. Call `CREATE_AWDIMENSION_ACCESS_FULL` for each of the cube's dimensions. This procedure creates the enablement scripts in temporary memory and runs the scripts to create the dimension views and, optionally, the OLAP Catalog metadata. The scripts delete any previous views and metadata before creating new views and metadata.

4. Call the procedure `CREATE_AWCUBE_ACCESS_FULL` to create the fact views for the cube. This procedure accomplishes the same basic steps as the corresponding procedure for dimensions.

## The OLAP API Enabler Procedures

The OLAP API enabler procedures are listed in Table 1-11.

Table 1-11 The OLAP API Enabler Procedures

Procedure | Description
--- | ---
CREATE_AWCUBE_ACCESS Procedure | Creates a script that enables relational access to a cube in an analytic workspace.
CREATE_AWCUBE_ACCESS_FULL Procedure | Enables relational access to a cube in an analytic workspace.
CREATE_AWDIMENSION_ACCESS Procedure | Creates a script that enables relational access to a dimension in an analytic workspace.
CREATE_AWDIMENSION_ACCESS_FULL Procedure | Enables relational access to a dimension in an analytic workspace.
DELETE_AWCUBE_ACCESS Procedure | Creates a script that deletes the enablement views and metadata for a cube in an analytic workspace.
DELETE_AWCUBE_ACCESS_ALL Procedure | Deletes the enablement views and metadata for a cube in an analytic workspace.
DELETE_AWDIMENSION_ACCESS Procedure | Creates a script that deletes the enablement views and metadata for a dimension in an analytic workspace.
DELETE_AWDIMENSION_ACCESS_ALL Procedure | Deletes the enablement views and metadata for a dimension in an analytic workspace.
REFRESH_AWCUBE_VIEW_NAME Procedure | Creates metadata in the analytic workspace to support user-defined view names for a cube.
REFRESH_AWDIMENSION_VIEW_NAME Procedure | Creates metadata in the analytic workspace to support user-defined view names for a dimension.
SET_AWCUBE_VIEW_NAME Procedure | Replaces the system-generated names for the views of an analytic workspace cube.
SET_AWDIMENSION_VIEW_NAME Procedure | Replaces the system-generated names for the views of an analytic workspace dimension.

Note: If you capture the SQL generated by Analytic Workspace Manager and use it to create your own scripts, you will need to edit the enablement procedure calls. Analytic Workspace Manager uses different versions of the enablement procedures. In your scripts, you must use the syntax described in this manual.

## Disabling Relational Access

The enablement procedures automatically delete any previous generation of views and OLAP Catalog metadata. However, in some circumstances, you might want to drop the views and metadata without re-creating them. In particular, if you drop the workspace cube or the workspace itself, you will need to clean up the orphaned views and metadata.

In this case, you can run the `DELETE_AWDIMENSION_ACCESS` and `DELETE_AWCUBE_ACCESS` procedures to generate scripts that will drop the views and metadata that enable relational access to the cube. These scripts do not delete any enablement metadata that is stored within the analytic workspace.

To delete all the enablement views and metadata for a dimension or a cube, use `DELETE_AWCUBE_ACCESS_ALL` and `DELETE_AWDIMENSION_ACCESS_ALL`.

## Specifying Names for Dimension Views

The `CREATE_AWDIMENSION_ACCESS` and `CREATE_AWDIMENSION_ACCESS_FULL` procedures create metadata in the analytic workspace related to enablement. This metadata includes a set of default names for the enablement views.

If you want to specify your own view names, you must refresh this metadata by calling `REFRESH_AWDIMENSION_VIEW_NAME`. Then call `SET_AWDIMENSION_VIEW_NAME` to specify the names of the views.

Whenever you re-create the views, new view names are generated. If you have previously created your own names, the refresh process uses them as the basis for the new names.

The default view name for a dimension is: `aaaa_bbbbb_ccccc_ddddd#``view`, where:

`aaaa` is the first four characters of the analytic workspace owner

`bbbbb` is the first five characters of the analytic workspace name

`ccccc` is the first five characters of the analytic workspace dimension name

`ddddd` is the first five characters of the analytic workspace hierarchy name

`#` is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.

Default names are also generated for the abstract objects (ADTs) populated by `OLAP_TABLE`. For example, the workspace dimension `AWGEOG`, in a workspace called `AWTEST` in the `XADEMO` schema could have the following system-generated names for the `STANDARD` hierarchy.

Default Name | Description
--- | ---
`XADE_AWTES_AWGE0_STAND34VIEW` | Name of the relational view
`XADE_AWTES_AWGEOG34OBJ` | Name of the abstract object that defines a row in the abstract table of objects populated by `OLAP_TABLE`
`XADE_AWTES_AWGEOG34TBL` | Name of the abstract table type populated by `OLAP_TABLE`

## Specifying Names for Fact Views

The `CREATE_AWCUBE_ACCESS` and `CREATE_AWCUBE_ACCESS_FULL` procedures create metadata in the analytic workspace related to enablement. This metadata includes a set of default names for the enablement views.

If you want to specify your own view names, you must refresh this metadata by calling `REFRESH_AWCUBE_VIEW_NAME`. Then call `SET_AWCUBE_VIEW_NAME` to specify the names of the views.

Whenever you re-create the views, new view names are generated. If you have previously created your own names, the refresh process uses them as the basis for the new names.

The default view name for a cube is: `aaaa_bbbbb_cccccccc#``view`, where:

`aaaa` is the first four characters of the analytic workspace owner

`bbbbb` is the first five characters of the analytic workspace name

`cccccccc` is the first eight characters of the analytic workspace cube name

`#` is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.

Default names are also generated for the abstract objects (ADTs) populated by `OLAP_TABLE`. For example, the workspace cube `AWCUBE`, in a workspace called `AWTEST` in the `XADEMO` schema could have the following system-generated names.

Default Name | Description
--- | ---
`XADE_AWTES_AWCUBE8VIEW` | Name of the relational fact view for the first hierarchy combination.
`XADE_AWTES_AWCUBE9VIEW` | Name of the relational fact view for the second hierarchy combination.
`XADE_AWTES_AWCUBE10VIEW` | Name of the relational fact view for the third hierarchy combination.
`XADE_AWTES_AWCUBE11VIEW` | Name of the relational fact view for the fourth hierarchy combination.
`XADE_AWTES_AWCUBE7OBJ` | Name of the abstract object that defines a row in the abstract table of objects populated by `OLAP_TABLE`
`XADE_AWTES_AWCUBE7TBL` | Name of the abstract table type populated by `OLAP_TABLE`

## Column Structure of Dimension Views

The enablement process generates a separate view for each dimension hierarchy. For example, a workspace cube with the four dimensions shown in Table 1-12 would have six separate dimension views since two of the dimensions have two hierarchies.

Table 1-12 Sample Dimension Hierarchies

Dimensions | Hierarchies | Number of Views
--- | --- | ---
`geography` | `standard` `consolidated` | 2
`product` | `standard` | 1
`channel` | `standard` | 1
`time` | `standard` `ytd` | 2

The dimension views are level-based, and they include the full lineage of every level value in every row. This type of dimension table is considered solved, because the fact table related to this dimension includes embedded totals for all level combinations.

Each dimension view contains the columns described in Table 1-13.

Table 1-13 Dimension View Columns

Column | Description
--- | ---
ET key | The embedded-total key column stores the value of the lowest populated level in the row.
Parent ET key | The parent embedded-total key column stores the parent of each ET key value.
GID | The grouping ID column identifies the hierarchy level associated with each row, as described in "Grouping ID Column".
Parent GID | The parent grouping ID column stores the parent of each GID value.
level columns | A column for each level of the dimension hierarchy. These columns provide the full ancestry of each dimension member within a single row.
level attribute columns | A column for each level attribute.

### Sample Dimension View

For a standard geography hierarchy with levels for `TOTAL_US`, `REGION`, and `STATE`, the dimension view would contain columns like the ones that follow. Level attribute columns would also be included.

    GID PARENT_GID  ET KEY     PARENT_ET_KEY TOTAL_US  REGION      STATE
    --- ----------  ------     ------------  --------  ------      -----
    0   1           MA         Northeast     USA       Northeast   MA
    0   1           NY         Northeast     USA       Northeast   NY
    0   1           GA         Southeast     USA       Southeast   GA
    0   1           CA         Southwest     USA       Southwest   CA
    0   1           AZ         Southwest     USA       Southwest   AZ
    1   3           Northeast  USA           USA       Northeast
    1   3           Southeast  USA           USA       Southeast
    1   3           Southwest  USA           USA       Southwest
    3   NA          USA        NA            USA

### Grouping ID Column

The GID identifies the hierarchy level associated with each row by assigning a zero to each non-null value and a one to each null value in the level columns. The resulting binary number is the value of the GID.

For example, a GID of 1 is assigned to a row with the following three levels.

    TOTAL_US  REGION      STATE
    --------  ------      -----
    USA       Southwest
    0         0           1

A GID of 3 is assigned to a row with the following five levels.

    TOTAL_GEOG COUNTRY REGION     STATE  CITY
    ---------- ------- -------    ------ -------
    World      USA     Northeast
    0          0       0           1      1

## Column Structure of Fact Views

The `CREATE_AWCUBE_ACCESS` procedure generates a separate view for each dimension/hierarchy combination. For example, an analytic workspace cube with the four dimensions shown in Table 1-12, would have four separate fact views, one for each hierarchy combination show in Table 1-14.

Table 1-14 Sample Dimension/Hierarchy Combinations

Geography Dim | Product Dim | Channel Dim | Time Dim
--- | --- | --- | ---
`geography/standard` | `product/standard` | `channel/standard` | `time/standard`
`geography/standard` | `product/standard` | `channel/standard` | `time/ytd`
`geography/consolidated` | `product/standard` | `channel/standard` | `time/standard`
`geography/consolidated` | `product/standard` | `channel/standard` | `time/ytd`

The fact views are fully solved. They contain embedded totals for all level combinations. Each view has columns for the cube's measures, and key columns that link the fact view with its associated dimension views.

Each fact view contains the columns described in Table 1-15.

Table 1-15 Fact View Columns

Column | Description
--- | ---
ET key for each dimension/hierarchy | The ET key columns are foreign keys that map to the primary keys of the associated dimension tables, and are used to join the measure table with the dimension tables.
GID for each dimension/hierarchy | The GID column provides grouping IDs needed by the OLAP API for optimal response time. It is identical to the GID column of the associated dimension table.
measure columns | Columns for each of the cube's measures.
`R2C` | A column that stores information used by the single-row functions. See Chapter 30, "OLAP_EXPRESSION" and "Limit Map: ROW2CELL Clause".
`CUST_MEAS_TEXT` n | 100 sequentially numbered empty columns with a data type of `VARCHAR2(1000)`.
`CUST_MEAS_NUM` n | 100 sequentially numbered empty columns with a data type of `NUMBER(38,6)`.

## Example: Enable a Workspace Cube for Relational Access

The following example creates, refreshes, and enables a cube `AWUSR.AWTEST` based on the source cube `XADEMO.ANALYTIC_CUBE`.

Example 1-5 Create, Refresh, and Enable a Cube

    -- SET UP
    set serveroutput on size 1000000
    execute cwm2_olap_manager.set_echo_on;

    create or replace directory myscripts as '/users/myxademo/myscripts';
    execute cwm2_olap_manager.begin_log ('MYSCRIPTS' , 'awtest.log');

    --- CREATE AW
    execute dbms_aw.execute ('aw create ''AWTEST''');

    -- CREATE DIMENSIONS
    execute dbms_awm.create_awdimension
              ('XADEMO','CHANNEL',   'AWUSR', 'AWTEST', 'AWCHAN');
    execute dbms_awm.create_awdimension
              ('XADEMO','GEOGRAPHY', 'AWUSR', 'AWTEST', 'AWGEOG');
    execute dbms_awm.create_awdimension
              ('XADEMO','PRODUCT',   'AWUSR', 'AWTEST', 'AWPROD');
    execute dbms_awm.create_awdimension
              ('XADEMO','TIME',      'AWUSR', 'AWTEST', 'AWTIME');

    -- CREATE CUBE
    execute dbms_awm.create_awcube
              ('XADEMO', 'ANALYTIC_CUBE','AWUSR', 'AWTEST','AWCUBE');

    -- REFRESH DIMENSIONS
    execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWCHAN');
    execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWGEOG');
    execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWPROD');
    execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWTIME');

    -- REFRESH CUBE
    execute dbms_awm.refresh_awcube ('AWUSR', 'AWTEST', 'AWCUBE');

    -- SET DIMENSION VIEW NAMES
    exec dbms_awm.refresh_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awprod');
    exec dbms_awm.refresh_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awchan');
    exec dbms_awm.refresh_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awgeog');
    exec dbms_awm.refresh_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awtime');
    exec dbms_awm.set_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awprod', 'standard',     'prod_std_view');
    exec dbms_awm.set_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awchan', 'standard',     'chan_std_view');
    exec dbms_awm.set_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awgeog', 'consolidated', 'geog_csd_view');
    exec dbms_awm.set_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awgeog', 'standard',     'geog_std_view');
    exec dbms_awm.set_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awtime', 'standard',     'time_std_view');
    exec dbms_awm.set_awdimension_view_name
              ('AWUSR', 'AWTEST', 'awtime', 'ytd',          'time_ytd_view');

    -- SET CUBE VIEW NAMES
    exec dbms_awm.refresh_awcube_view_name
              ('AWUSR', 'AWTEST', 'awcube');
    exec dbms_awm.set_awcube_view_name
              ('AWUSR', 'AWTEST', 'awcube', 1, 'AWCUBE_view1');
    exec dbms_awm.set_awcube_view_name
              ('AWUSR', 'AWTEST', 'awcube', 2, 'AWCUBE_view2');
    exec dbms_awm.set_awcube_view_name
              ('AWUSR', 'AWTEST', 'awcube', 3, 'AWCUBE_view3');
    exec dbms_awm.set_awcube_view_name
              ('AWUSR', 'AWTEST', 'awcube', 4, 'AWCUBE_view4');

    -- ENABLE DIMENSIONS
    exec dbms_awm.create_AWdimension_access
              ('AWUSR', 'AWTEST', 'awprod', 'olap',
               'MYSCRIPTS', 'awprod_views.sql', 'w');
    exec dbms_awm.create_AWdimension_access
              ('AWUSR', 'AWTEST', 'awchan', 'olap',
               'MYSCRIPTS', 'awchan_views.sql', 'w');
    exec dbms_awm.create_AWdimension_access
              ('AWUSR', 'AWTEST', 'awgeog', 'olap',
               'MYSCRIPTS', 'awgeog_views.sql', 'w');
    exec dbms_awm.create_AWdimension_access
              ('AWUSR', 'AWTEST', 'awtime', 'olap',
               'MYSCRIPTS', 'awtime_views.sql', 'w');

    -- ENABLE CUBE
    exec dbms_awm.create_AWcube_access
              ('AWUSR', 'AWTEST', 'awcube', 'olap',
               'MYSCRIPTS', 'awcube_views.sql', 'w');

    -- COMMIT and WRAPUP
    commit;
    execute cwm2_olap_manager.end_log;

The following queries show the resulting workspace cube and dimensions with their source cubes and dimensions in the OLAP Catalog.

    select * from all_olap2_aw_dimensions where AW_OWNER = 'AWUSER';

    AW_OWNER  AW_NAME   AW_LOGICAL_NAME AW_PHYSICAL_OBJECT SOURCE_OWNER  SOURCE_NAME
    --------- --------- --------------- ------------------ ------------- ---------------
    AWUSER     AWTEST    AWCHAN          AWCHAN             XADEMO        CHANNEL
    AWUSER     AWTEST    AWGEOG          AWGEOG             XADEMO        GEOGRAPHY
    AWUSER     AWTEST    AWPROD          AWPROD             XADEMO        PRODUCT
    AWUSER     AWTEST    AWTIME          AWTIME             XADEMO        TIME

    select * from all_olap2_aw_CUBEs where AW_OWNER = 'AWUSER';

    AW_OWNER AW_NAME  AW_LOGICAL_NAME AW_PHYSICAL_OBJECT  SOURCE_OWNER  SOURCE_NAME
    -------- -------- --------------- ------------------- ------------- ---------------
    AWUSER    AWTEST   AWCUBE          AWCUBE              XADEMO        ANALYTIC_CUBE

The following query shows the system names and user names for the dimension enablement views.

    select * from all_aw_dim_ENABLED_VIEWS where AW_OWNER = 'AWUSER';

    AW_OWNER AW_NAME  DIMENSION_ HIERARCHY_   SYSTEM_VIEWNAME                USER_VIEWNAME
    -------- -------- ---------- ----------   ----------------------------   ----------------
    AWUSER    AWTEST   AWCHAN     STANDARD     AWUS_AWTES_AWCHA_STAND144VIEW  CHAN_STD_VIEW
    AWUSER    AWTEST   AWGEOG     CONSOLIDATED AWUS_AWTES_AWGEO_CONSO145VIEW  GEOG_CSD_VIEW
    AWUSER    AWTEST   AWGEOG     STANDARD     AWUS_AWTES_AWGEO_STAND146VIEW  GEOG_STD_VIEW
    AWUSER    AWTEST   AWPROD     STANDARD     AWUS_AWTES_AWPRO_STAND147VIEW  PROD_STD_VIEW
    AWUSER    AWTEST   AWTIME     STANDARD     AWUS_AWTES_AWTIM_STAND148VIEW  TIME_STD_VIEW
    AWUSER    AWTEST   AWTIME     YTD          AWUS_AWTES_AWTIM_YTD149VIEW    TIME_YTD_VIEW

The following query shows the system names and user names for the cube enablement views. Included are the hierarchy combination numbers, in this case 1 - 4, and the hierarchy strings, consisting of each unique combination of dimension hierarchies for this cube.

    select * from all_aw_CUBE_ENABLED_VIEWS where AW_OWNER = 'AWUSER';

    AW_OWN AW_NA  CUBE_NAM  HIER  HIERCOMBO_STR                         SYSTEM_VIEWNAME            USER_VIEWNAME
    ------ ------ --------  ----  -----------------------------------  -----------------------   -----------
    AWUSER  AWTEST  AWCUBE   1     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE151VIEW  AWCUBE_VIEW1
                                  /HIER:CONSOLIDATED;DIM:AWPROD/HIER:
                                  STANDARD;DIM:AWTIME/HIER:STANDARD
    AWUSER  AWTEST  AWCUBE   2     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE152VIEW  AWCUBE_VIEW2
                                  /HIER:CONSOLIDATED;DIM:AWPROD/HIER:
                                  STANDARD;DIM:AWTIME/HIER:YTD
    AWUSER   AWTEST  AWCUBE  3     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE153VIEW  AWCUBE_VIEW3
                                  /HIER:STANDARD;DIM:AWPROD/HIER:STAN
                                  DARD;DIM:AWTIME/HIER:STANDARD
    AWUSER   AWTEST  AWCUBE  4     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE154VIEW   AWCUBE_VIEW4
                                  /HIER:STANDARD;DIM:AWPROD/HIER:STAN
                                  DARD;DIM:AWTIME/HIER:YTD

The final step is to run the enablement scripts to generate the views for the analytic workspace cube. The scripts produced by this example are described as follows.

Script | Description
--- | ---
`awprod_views.sql` | Creates an abstract object, a table of objects, and a view for the `PRODUCT` dimension. Also creates and validates an OLAP Catalog dimension `AWUSER.AWPROD` that maps to the view.
`awchan_views.sql` | Creates an abstract object, a table of objects, and a view for the `CHANNEL` dimension. Also creates and validates an OLAP Catalog dimension `AWUSER.AWCHAN` that maps to the view.
`awgeog_views.sql` | Creates an abstract object, a table of objects, and a view for each hierarchy of the `GEOGRAPHY` dimension. Also creates and validates an OLAP Catalog dimension `AWUSER.AWGEOG` that maps to the view.
`awtime_views.sql` | Creates an abstract object, a table of objects, and a view for each hierarchy of the TIME dimension. Also creates and validates an OLAP Catalog dimension `AWUSER.AWTIME` that maps to the view.
`awcube_views.sql` | Creates an abstract object, a table of objects, and a separate view for each hierarchy combination of the `AWCUBE` cube. Also creates and validates an OLAP Catalog cube `AWUSER.AWCUBE` that maps to the view.